﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Common
{
    public class XlsHelper : IDisposable
    {
        private string filename = null;
        private IWorkbook workbook = null;
        private FileStream fs = null;
        private bool disposed;

        public XlsHelper(string filename)
        {
            this.filename = filename;
            disposed = false;
        }
        
        /// <summary>
        /// 将DataTable的数据导入到excel中
        /// </summary>
        /// <param name="dt">要导入的Datatable</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="HDR">是否导入Datatable的列名</param>
        /// <returns>导入的数据行数（包含列名那行）</returns>
        public int DatatabletoExcel(DataTable dt, string sheetname = "Sheet1", bool HDR = true)
        {
            int count = 0;
            ISheet sheet = null;
            using (fs = new FileStream(filename, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook();//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook();//xls, 2003 format
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetname);
                }
                else return -1;
                if (HDR)
                {
                    IRow row = sheet.CreateRow(0);
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            row.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
                        }
                        count = 1;
                    }
                }
                else count = 0;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(count);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                    count++;
                }
                workbook.Write(fs);
            }

            return count;
        }

      

        public string[] GetColnames(ISheet sheet,int titleRowIndex=0)
        {
            List<string> colnames = new List<string>();
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(titleRowIndex);
                int cellCount = firstRow.LastCellNum;//最后一个cell的编号，即为总列数
                int startCell = firstRow.FirstCellNum;
                for (int i = startCell; i < cellCount; i++)
                {
                    ICell cell = firstRow.GetCell(i);
                    if (cell != null)
                    {
                        string cellValue;
                        if (cell.CellType != CellType.String)
                            cellValue = cell.NumericCellValue.ToString();
                        else
                            cellValue = cell.StringCellValue;
                        colnames.Add(cellValue);
                    }
                }
            }
            return colnames.ToArray();
        }
        public string[] GetColnames(int sheetIndex ,int titleRowIndex=0)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            using (fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return new string[0];
                if (workbook.NumberOfSheets > sheetIndex)
                {
                    sheet = workbook.GetSheetAt(sheetIndex);
                    return GetColnames(sheet, titleRowIndex);
                }
                else
                {
                    return new string[0];
                }
            }
        }

        /// <summary>
        /// 将指定sheet的数据导入datatable
        /// </summary>
        /// <param name="sheetIndex">sheet编号，从0开始</param>
        /// <param name="HDR">是否有列标题</param>
        /// <param name="HDC">是否有行标题</param>
        /// <returns></returns>
        public DataTable ExcelToDatatable(int sheetIndex = 0, bool HDR = true, bool HDC = false)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            using (fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return data;
                if (workbook.NumberOfSheets > sheetIndex)
                {
                    sheet = workbook.GetSheetAt(sheetIndex);
                    data = ReadData(sheet, HDR, HDC);
                }
               
            }

            return data;
        }
        /// <summary>
        /// 读取excel文件中的指定表，指定行为标题，指定数据开始行，指定字段的数据，读回的数据按指定的列标题存放
        /// </summary>
        /// <param name="sheetIndex">sheet编号，从0开始</param>
        /// <param name="titleRow">标题所在行</param>
        /// <param name="dataStartRow">数据开始行</param>
        /// <param name="selectAndChangetitles">传入的标题字典，选取字典的key为datatable的列标题，并读取excel中列标题为对应value的值</param>
        /// <returns></returns>
        public DataTable ExcelToDatatable(int sheetIndex,int titleRow,int dataStartRow,Dictionary<string,string> selectAndChangetitles)
        {
            //1.初始化构建datatable列
            DataTable table = new DataTable();
            foreach (var item in selectAndChangetitles)
            {
                table.Columns.Add(item.Key);
            }
            //打开文件
            using (fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return table;
                
                Dictionary<string, int> colPositions = new Dictionary<string, int>();
                //存放每列数据对应的excel数据列
                if (workbook.NumberOfSheets > sheetIndex)
                {//容错处理
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    if (sheet != null)
                    {
                        //2.分析标题行，找到标题对应的列索引
                        var colTitles = GetColnames(sheetIndex, titleRow).ToList();//读出所有的列标题
                        foreach (var item in selectAndChangetitles)
                        {
                            int index = colTitles.IndexOf(item.Value);
                            colPositions.Add(item.Key, index);
                        }
                        //3.逐行扫描，读取每列对应的单元格，生成datarow
                        for (int i = dataStartRow; i <= sheet.LastRowNum; i++)
                        {
                            DataRow row = table.NewRow();
                            for (int j = 0; j < table.Columns.Count; j++)
                            {
                                row[j] = ReadCellString(sheet, i, colPositions[table.Columns[j].ColumnName]);
                            }
                            //4.datatable加入行
                            table.Rows.Add(row);
                        }
                    }

                }
               
            }
            

            return table;
        }

        private string ReadCellString(ISheet sheet, int rowIndex, int columnIndex)
        {
            if (rowIndex < 0 || columnIndex < 0) return "";
            IRow row = sheet.GetRow(rowIndex);
            ICell cell = row.GetCell(columnIndex);
            string data=null;
            if (cell != null)
            {
                
                if (cell.CellType == CellType.String)
                    data = cell.StringCellValue;//.ToString();

                else
                    data= cell.NumericCellValue.ToString();
            }
            return data;
        }
       

        /// <summary>
        /// EXCEL数据导入DataTable
        /// </summary>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="HDR">是否导入列标题</param>
        /// <param name="HDC">是否有行标题</param>
        /// <returns></returns>
        public DataTable ExcelToDatatable(string sheetname, bool HDR = true, bool HDC = false)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();

            using (fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                if (!string.IsNullOrEmpty(sheetname))
                {
                    sheet = workbook.GetSheet(sheetname);
                    if (sheet == null)
                    {//如果没找到指定的sheet，则尝试获取第一个sheet
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    data = ReadData(sheet, HDR, HDC);
                }
            }

            return data;

        }
        private DataTable ReadData(ISheet sheet, bool HDR, bool HDC)
        {
            DataTable data = new DataTable();
            int startRow = 0;
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum;//最后一个cell的编号，即为总列数
                int startCell = firstRow.FirstCellNum;
                if (HDC) startCell++;
                if (HDR)
                {
                    for (int i = startCell; i < cellCount; i++)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue;
                            if (cell.CellType != CellType.String)
                                cellValue = cell.NumericCellValue.ToString();
                            else
                                cellValue = cell.StringCellValue;
                            if (!string.IsNullOrEmpty(cellValue))
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    cellCount = data.Columns.Count;//单元数量修正为标题栏数量
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                    for (int i = startCell; i < cellCount; i++)
                    {
                        data.Columns.Add("Col" + i.ToString());
                    }
                }
                int rowCount = sheet.LastRowNum;

                for (int i = startRow; i <= rowCount; i++)
                {//遍历数据行
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue;//没有数据的行默认是null
                    System.Data.DataRow dataRow = data.NewRow();
                    if (row.LastCellNum >= cellCount)
                    {//要求数据行单元格比标题数多，以免读出来的数据无法转换
                        bool empty = true;
                        for (int j = startCell; j < cellCount; j++)
                        {

                            ICell cell = row.GetCell(j);
                            // if (cell.CellType != CellType.Blank)//没有数据的单元默认是blank
                            if (cell != null)
                            {
                                empty = false;
                                if (cell.CellType == CellType.String)
                                    dataRow[j - startCell] = cell.StringCellValue;//.ToString();

                                else
                                    dataRow[j - startCell] = cell.NumericCellValue;
                            }

                        }
                        if (!empty)
                            data.Rows.Add(dataRow);
                    }

                }
            }
            return data;
        }
        public void EditCellValue(int sheetIndex, int row, int col, string value)
        {
            ISheet sheet = GetSheet(sheetIndex);
            if (sheet == null) return;
            ICell cell = sheet.GetRow(row).GetCell(col);
            cell.SetCellValue(value);
        }
        public string GetCellValueString(int sheetIndex, int row, int col)
        {
            ISheet sheet = GetSheet(sheetIndex);
            if (sheet == null) return "";
            ICell cell = sheet.GetRow(row).GetCell(col);
            if (cell != null)
            {
                string cellValue;
                if (cell.CellType != CellType.String)
                    cellValue = cell.NumericCellValue.ToString();
                else
                    cellValue = cell.StringCellValue;
                return cellValue;
            }
            return "";
        }
        public ISheet GetSheet(int sheetIndex = 0)
        {
            ISheet sheet = null;

            using (fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return sheet;
                if (workbook.NumberOfSheets > sheetIndex)
                {
                    sheet = workbook.GetSheetAt(sheetIndex);
                }
                return sheet;
            }
        }
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (fs != null) fs.Close();
                }
                fs = null;
                disposed = true;
            }
        }
        #region 静态方法
        public static List<string> GetTitles(string filename)
        {

            List<string> titles = new List<string>();
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) workbook = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xls")) workbook = new HSSFWorkbook(fs);//xls, 2003 format
                else return titles;
                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    titles.Add(workbook.GetSheetName(i));
                }
            }            
            return titles;
        }
        /// <summary>
        /// 用于将excel表格中列索引转成列号字母，从A对应0开始
        /// </summary>
        /// <param name="index">列索引,从0开始</param>
        /// <returns>列号</returns>
        public static string IndexToColumn(int index)
        {

            if (index < 0)
            {
                throw new Exception("Invalid parameter");
            }
            string column = string.Empty;
            do
            {
                if (column.Length > 0)
                {
                    index--;
                }
                column = ((char)(index % 26 + (int)'A')).ToString() + column;
                index = (int)((index - index % 26) / 26);
            } while (index > 0);
            return column;
        }
        #endregion
    }
}
